﻿/*
 *  Sqlite ORM - PUBLIC DOMAIN LICENSE
 *  Copyright (C)  2010-2012. Ian Quigley
 *  
 *  This source code is provided 'As is'. You bear the risk of using it.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using SqliteORM.Tests.Model;
using Xunit;
using SubSpec;

namespace SqliteORM.Tests
{
	public class WriteRead
	{
		[Specification]
		public void UsingSimpleValues()
		{
            "Given an empty database".Context(
                () =>
                {
                    Context.Initialise();
                    DbConnection.Initialise(Context.ConnectionString);
                });

			"when we add values to a table".Do( 
                () => {
			            using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
			            {
				            adapter.CreateUpdate( 1, "test" );
                        }
                });

            "expect that we can retrieve the result".Assert(
                () => {
                        using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                        {
				            TableIdString result = adapter.Read( 1 );

            				Assert.NotNull( result );
			            	Assert.Equal(1, result.Id);
				            Assert.Equal("test", result.Text);
			            } });
		}

        [Specification]
        public void UsingAClassInstance()
        {
            "Given an empty database".Context(
                () =>
                {
                    Context.Initialise();
                    DbConnection.Initialise(Context.ConnectionString);
                });

            TableIdString test = new TableIdString() { Id = 23, Text = "Hello" };

            "when we store a class ".Do(
                () =>
                {
                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        adapter.CreateUpdate(test);
                    }
                });

            "expect that we can retrieve the class".Assert(
                () =>
                {
                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        TableIdString result = adapter.Read( test.Id );

                        Assert.NotNull(result);
                        Assert.Equal(test.Id, result.Id);
                        Assert.Equal(test.Text, result.Text);
                    }
                });
        }

		[Specification]
		public void ReadTableFindWithTooManyParams()
		{
			"Given an empty database".Context(
				() =>
				{
					Context.Initialise();
					DbConnection.Initialise(Context.ConnectionString);
				});

			TableIdString test = new TableIdString() { Id = 23, Text = "Hello" };

			Exception ex = default(Exception);
			"when we call Find with too many parameters".Do(
				() =>
				{
					ex = Record.Exception( ( ) =>
					                       	{
					                       		using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					                       		{
													adapter.Read( 1, 2, 3, 4 );
					                       		}
					                       	} );
				});

			"expect an exception".Assert(
				() => Assert.NotNull( ex ) );
		}

        [Specification]
        public void CreateUpdateRead()
        {
            "Given a database with a row of data".Context(
                () =>
                {
                    Context.Initialise();
                    DbConnection.Initialise(Context.ConnectionString);

                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                        adapter.CreateUpdate(new TableIdString() { Id = 1, Text = "AA" });
                });

            TableIdString value = default(TableIdString);
                "when we Update and Read back the data".Do(
                () =>
                {
                        using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                        {
                            
                            adapter.CreateUpdate(1, "BB");
                            value = adapter.Read(1);
                        }
                    });
                
            "expect the value has changed".Assert (
                () => Assert.Equal("BB", value.Text));
        }



        [Specification]
        public void UpdateAClassKnownDataNotChanged()
        {
            TableIdString test = new TableIdString() { Id = 23, Text = "Hello" };
            TableIdString known = new TableIdString() { Id = 19, Text = "Kimple Dog Bah" };

            "Given an table with a row of data".Context(
                () =>
                {
                    Context.Initialise();
                    DbConnection.Initialise(Context.ConnectionString);

                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        adapter.CreateUpdate(test);
                        adapter.CreateUpdate(known);
                    }
                });


            "when we store the updated class".Do(
                () =>
                {
                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        test.Text = "Goodbye";
                        adapter.CreateUpdate(test);
                    }
                });

            "expect the updated data was stored".Assert(
                () =>
                {
                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        TableIdString result = adapter.Read(test.Id);

                        Assert.NotNull(result);
                        Assert.Equal(test.Id, result.Id);
                        Assert.Equal(test.Text, result.Text);
                    }
                });

            "expect that the known data is unchanged".Assert(
                () =>
                {
                    using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
                    {
                        TableIdString result = adapter.Read(known.Id);

                        Assert.NotNull(result);
                        Assert.Equal(known.Id, result.Id);
                        Assert.Equal(known.Text, result.Text);
                    }
                });

        }

		[Specification]
		public void ReadWhereValueGreaterLessEqualEtc()
		{
			List<TableIdString> known = new  List<TableIdString>() 
			{
				new	TableIdString() { Id = 1, Text = "1"},
				new	TableIdString() { Id = 2, Text = "2"},
				new	TableIdString() { Id = 3, Text = "3"},
				new	TableIdString() { Id = 4, Text = "4"},
				new	TableIdString() { Id = 5, Text = "5"}
			};

			"Given an table with known data".Context(
				() =>
				{
					Context.Initialise();
					DbConnection.Initialise(Context.ConnectionString);

					using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					{
						foreach (var row in known)
							adapter.CreateUpdate(row);
					}
				});

			// Bit naughty not having an 'act', and putting them into the observers.
			"".Do( ( ) => { } );

			"expect querying 'Id less than 2' returns valid results".Observation(() =>
			{
				List<TableIdString> results;
				using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					results = adapter.Select().Where(Where.LessThan("Id", 2)).ToList();

				Assert.True(results.All(t => t.Id < 2));
			});

			"expect querying 'Id less than or equal 2' returns valid results".Observation(() =>
			{
				List<TableIdString> results;
				using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					results = adapter.Select().Where(Where.LessOrEqual("Id", 2)).ToList();

				Assert.True(results.All(t => t.Id <= 2));
			});

			"expect querying 'Id greater than or equal 2' returns valid results".Observation(() =>
			{
				List<TableIdString> results;
				using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					results = adapter.Select().Where(Where.GreaterOrEqual("Id", 2)).ToList();

				Assert.True(results.All(t => t.Id >= 2));
			});

			"expect querying 'Id greater than 2' returns valid results".Observation(() =>
			{
				List<TableIdString> results;
				using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					results = adapter.Select().Where(Where.GreaterThan("Id", 2)).ToList();

				Assert.True(results.All(t => t.Id > 2));
			});
		}

		[Specification]
		public void ReadWhereAnd()
		{
			List<TableIdString> known = new List<TableIdString>()
			                            	{
			                            		new TableIdString() {Id = 1, Text = "A"},
			                            		new TableIdString() {Id = 2, Text = "A"},
			                            		new TableIdString() {Id = 3, Text = "B"},
			                            		new TableIdString() {Id = 4, Text = "B"},
			                            		new TableIdString() {Id = 5, Text = "A"}
			                            	};

			"Given an table with known data".Context(
				( ) =>
					{
						Context.Initialise();
						DbConnection.Initialise( Context.ConnectionString );

						using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
						{
							foreach (var row in known)
								adapter.CreateUpdate( row );
						}
					} );

			List<TableIdString> result = default(List<TableIdString>);

			"reading results WHERE Id < 4 AND Text ='B' ".Do(
				( ) =>
					{
						using (TableAdapter<TableIdString> adpater = TableAdapter<TableIdString>.Open())
						{
							result = adpater.Select().Where(Where.And(Where.LessThan("Id", 4), Where.Equal("Text", "B"))).ToList();
						}
					} );

			"expect results to contain data matching Where clause".Observation(
				( ) => Assert.True( result.All( t => t.Id < 4 && t.Text == "B" ) ) );
		}
	

		[Specification]
		public void ReadWhereOr()
		{
			List<TableIdString> known = new List<TableIdString>()
			                            	{
			                            		new TableIdString() {Id = 1, Text = "A"},
			                            		new TableIdString() {Id = 2, Text = "A"},
			                            		new TableIdString() {Id = 3, Text = "B"},
			                            		new TableIdString() {Id = 4, Text = "B"},
			                            		new TableIdString() {Id = 5, Text = "A"}
			                            	};

			"Given an table with known data".Context(
				( ) =>
					{
						Context.Initialise();
						DbConnection.Initialise( Context.ConnectionString );

						using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
						{
							foreach (var row in known)
								adapter.CreateUpdate( row );
						}
					} );

			List<TableIdString> result = default(List<TableIdString>);

			"reading results WHERE Id > 3 OR Text ='B' ".Do(
				( ) =>
					{
						using (TableAdapter<TableIdString> adpater = TableAdapter<TableIdString>.Open())
						{
							result = adpater.Select().Where(Where.Or(Where.GreaterThan("Id", 3), Where.Equal("Text", "B"))).ToList();
						}
					} );

			"expect results to contain data matching Where clause".Observation(
				( ) => Assert.True( result.All( t => t.Id > 3 || t.Text == "B" ) ) );
		}


		[Specification]
		public void ReadWhereOrAndOr()
		{
			List<TableIdString> known = new List<TableIdString>()
			                            	{
			                            		new TableIdString() {Id = 1, Text = "A"},
			                            		new TableIdString() {Id = 2, Text = "A"},
			                            		new TableIdString() {Id = 3, Text = "B"},
			                            		new TableIdString() {Id = 4, Text = "B"},
			                            		new TableIdString() {Id = 5, Text = "B"},
												new TableIdString() {Id = 6, Text = "A"},
												new TableIdString() {Id = 7, Text = "C"}
			                            	};

			"Given an table with known data".Context(
				() =>
				{
					Context.Initialise();
					DbConnection.Initialise(Context.ConnectionString);

					using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					{
						foreach (var row in known)
							adapter.CreateUpdate(row);
					}
				});

			List<TableIdString> result = default(List<TableIdString>);

			"reading results WHERE (Id < 4 AND Text ='B') OR (Id >= 6 AND Text='C') ".Do(
				() =>
				{
					using (TableAdapter<TableIdString> adpater = TableAdapter<TableIdString>.Open())
					{
						result = adpater.Select().Where( 
							Where.Or(
								Where.And(Where.LessThan("Id", 4), Where.Equal("Text", "B")),
								Where.And(Where.GreaterOrEqual("Id", 6), Where.Equal("Text", "C"))
								)
								).ToList();
					}
				});

			"expect results to contain data matching Where clause".Observation(
				() => Assert.True(result.All(t => (t.Id < 4 && t.Text == "B") || (t.Id >= 6 && t.Text == "C") )));
		}
	


		[Specification]
		public void ReadWhereStringLike()
		{
			List<TableIdString> known = new List<TableIdString>()
			                            	{
			                            		new TableIdString() {Id = 1, Text = "Aaabb"},
			                            		new TableIdString() {Id = 2, Text = "Aabba"},
			                            		new TableIdString() {Id = 3, Text = "Bbbabba"},
			                            		new TableIdString() {Id = 4, Text = "Babababa"},
			                            		new TableIdString() {Id = 5, Text = "AAAAAaaa"}
			                            	};

			"Given an table with known data".Context(
				() =>
				{
					Context.Initialise();
					DbConnection.Initialise(Context.ConnectionString);

					using (TableAdapter<TableIdString> adapter = TableAdapter<TableIdString>.Open())
					{
						foreach (var row in known)
							adapter.CreateUpdate(row);
					}
				});

			List<TableIdString> result = default(List<TableIdString>);

			"reading results WHERE Text Like '%bab%' ".Do(
				() =>
				{
					using (TableAdapter<TableIdString> adpater = TableAdapter<TableIdString>.Open())
					{
						result = adpater.Select().Where(Where.Like("Text", "%bab%")).ToList();
					}
				});

			"expect results to contain data matching Where clause".Observation(
				() => Assert.True(result.All(t => t.Text.Contains("bab"))));
		}
	}
}
